Creating and editing external data queries

External data queries are suitable for standard value lists that reside in existing external (to Meridian) OLE DB data sources such as SQL Server, Oracle, Excel, or text files. These can be useful for reusing project, customer, or related document data stored in other information systems.

Before you begin

You will need an OLE DB connection string and user account credentials for the external data source. You might need to consult with the data source’s system administrator to obtain this information.

To create or edit an external data query:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.
2. To delete a query, select it and on the Edit menu, select Delete.
3. To edit an existing query:

1. Select the query and click the Edit button.
2. Click the Query tab. The existing query parameters are shown.
3. If necessary, edit the existing value for Connection string or click the Connection String hyperlink to build a connection string. The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.
4. If necessary, edit the existing value for From. If the VBScript option is enabled, click the Meridian Enterprise Script Editor button to edit the VBScript expression. For information on using VBScript for configuration expressions, see the BlueCielo Meridian Enterprise VBScript API Reference.
   
   Tip    For more information on valid values for From, click the From hyperlink, which will display reference information from the Microsoft Developer Network (MSDN) SQL Server Developer Center website.

5. Click the Test button to preview the results.

4. To create a new query, on the Edit menu, select New Table or Query. The Select Data Source Type dialog box appears.
5. Select Query and click OK. A new query is added to the configuration tree and its property pages appear in the right pane.
6. On the General page, type a name for the query in Display Name. This name will not be visible to users. A default internal name is calculated in Name. Accept the default in most cases.
7. Click the Query page to define the query. The query’s parameter options appear.
8. Type a valid OLE DB connection string in Connection string or click the Connection String hyperlink to build a connection string. The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.

Note The user name and password typed in the Data Link Properties dialog are not shown in the Connection string text box for increased security.

9. Type the name of an existing table or view in the data source in From. A query may also be typed. To calculate the table name, view name, or query from existing vault properties, enable the VBScript option and click the Meridian Enterprise Script Editor button to build a VBScript expression. For information on using VBScript for configuration expressions, see the BlueCielo Meridian Enterprise VBScript API Reference.
   
   Tip    For more information on valid values for From, see Using the FROM Clause on the Microsoft Developer Network (MSDN) SQL Server Developer Center website.
10. Click the Test button to preview the results, which will appear in the data grid.
11. Click OK to save the query’s definition.

You may now create a VBScript function to retrieve data from the query, as described in the BlueCielo Meridian Enterprise VBScript API Reference.

Tip    Users cannot add new entries to an external data source from the Meridian client applications. The data can only be edited by the data source’s native application or by VBScript. To edit external data using VBScript, construct SQL INSERT or SQL UPDATE query statements and run them with the Vault.ExecSQL method described in the BlueCielo Meridian Enterprise VBScript API Reference.

Note     If the data source does not reside on the Meridian server, ensure that the account under which the AutoManager EDM Server service is run can access that location. If necessary, change the BlueCielo EDM Server service account as described in the BlueCielo Meridian Enterprise Administrator’s Guide.

Note    If SQL Server or Oracle is used for the database of the Meridian vault, do not attempt to store the tables used by these queries in the same database as the Meridian vault. Instead, create a new database in SQL Server or Oracle for the tables. BlueCielo ECM Solutions does not support storing query tables in the same database as the Meridian vault.